package cn.edu.scau.cmi.ema.util;
/*
 *
 *@author ojj
 *@date 2020/4/21 13:39
 *
 */

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

	private static final String EXTENSION_XLS = "xls";

	private static final String EXTENSION_XLSX = "xlsx";

	private static CellStyle cellStyle;


	public void testExportExcel() throws IOException {
		String path = "/home/yinglongyhy/workspace/ema/git/ema/ema/项目文档/静态适配数据/（2）静态适配数据-李丹-欧盟.xlsx";
		Workbook wb = getWorkbook(path);
		Sheet sheet = wb.getSheetAt(0);
		List<Row> headList = getRows(sheet, sheet.getFirstRowNum(), 1);
		List<Row> dataList = getRows(sheet, 2);

		exportXlsx(new File("/tmp/a.xlsx"), headList, dataList);
	}


	public void test() throws ParseException, IOException {
		String path = "/home/yinglongyhy/workspace/ema/git/ema/ema/项目文档/静态适配数据/（2）静态适配数据-李丹-欧盟.xlsx";
		Workbook wb = getWorkbook(path);
		Sheet sheet = wb.getSheetAt(0);
		List<Row> headList = getRows(sheet, sheet.getFirstRowNum(), 1);
		List<Row> dataList = getRows(sheet, 2);

//		for (Row row : headList) {
//			for (Cell cell : row) {
//				if (cell.getCellType() == CellType.NUMERIC)
//					System.out.print(cell.getNumericCellValue() + "==");
//				else if (cell.getCellType() == CellType.STRING)
//					System.out.print(cell.getStringCellValue() + "==");
//			}
//			System.out.println();
//		}
    	SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    	SimpleDateFormat sdfOut = new SimpleDateFormat("yyyy-MM-dd");
    	for (int i = 0; i < dataList.size(); i++) {
    		Row row = dataList.get(i);
    		System.out.printf("%-5d", i);
    		for (int j = row.getFirstCellNum() + 1; j < row.getLastCellNum(); j++) {
    			Cell cell = row.getCell(j);
    			if (j == 2) {
    				System.out.print(sdfOut.format(string2Calendar(cell.getStringCellValue(), sdf).getTime()) + "==");
    			} else if (cell.getCellType() == CellType._NONE) {
    				System.out.println("==");
    			}else if (cell.getCellType() == CellType.NUMERIC)
    				System.out.print(cell.getNumericCellValue() + "==");
    			else if (cell.getCellType() == CellType.STRING)
    				System.out.print(cell.getStringCellValue() + "==");
			}
    		System.out.println();
    	}
	}

	public static Calendar string2Calendar(String s, DateFormat sdf) throws ParseException {
		Date date = sdf.parse(s);

		Calendar calendar = Calendar.getInstance();

		calendar.setTime(date);

		return calendar;
	}

	/**
	 * 根据path创建Excel，并把表头和表数据写到Excel里
	 * @param file			保存的文件路径
	 * @param headRows		表头
	 * @param dataRows		表数据
	 * @throws IOException
	 */
	public static void exportXlsx(File file, List<Row> headRows, List<Row> dataRows) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();

		cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);

		XSSFSheet sheet = wb.createSheet();

		int rowNum = 0;

		copyRow(sheet, headRows, rowNum);
		copyRow(sheet, dataRows, rowNum + headRows.size());

//		File file = new File(path);
		if (file.exists()) {
			file.delete();
		}
		FileOutputStream ops = new FileOutputStream(file);
		wb.write(ops);
		ops.flush();
		ops.close();
	}

	/**
	 * 根据path创建Excel，并把表头和标红的表数据写到Excel里
	 * @param path			保存的文件路径
	 * @param headRows		表头
	 * @param dataRows		表数据
	 * @throws IOException
	 */
	public static void exportRedXlsx(String path, List<Row> headRows, List<Row> dataRows) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();

		cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		((XSSFCellStyle) cellStyle).getFont().getXSSFColor().setARGBHex("FFFF0000");


		XSSFSheet sheet = wb.createSheet();

		int rowNum = 0;

		copyRow(sheet, headRows, rowNum);
		copyRow(sheet, dataRows, rowNum + headRows.size());

		File file = new File(path);
		if (file.exists()) {
			file.delete();
		}
		FileOutputStream ops = new FileOutputStream(file);
		wb.write(ops);
		ops.flush();
		ops.close();
	}


	/**
	 * 把rows里的数据复制到sheet里
	 * @param destSheet			目标sheet
	 * @param rows				要导入的数据
	 * @param startRowNum		从目标sheet的第startRowNum行开始导入，startRowNum从0开始
	 */
	private static void copyRow(XSSFSheet destSheet, List<Row> rows, int startRowNum) {
		for (Row row : rows) {
			XSSFRow newRow = destSheet.createRow(startRowNum);
			startRowNum++;
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				Cell newCell = newRow.createCell(i);

				switch (cell.getCellType()) {
				case NUMERIC:
					newCell.setCellValue(cell.getNumericCellValue());
					break;
				case STRING:
					newCell.setCellValue(cell.getStringCellValue());
					break;

				default:
					break;
				}
				newCell.setCellStyle(cellStyle);
			}
		}
	}
	//TODO　获取从指定行到最后的行
	public static List<Row> getRows(Sheet sheet, int startRowNum) {
		return getRows(sheet, startRowNum, sheet.getLastRowNum());
	}

	/**
	 *
	 * @param sheet
	 * @param startRowNum 开始的行数，从0开始，包含这一行
	 * @param lastRowNum  最后一行，包含这一行
	 * @return
	 */
	public static List<Row> getRows(Sheet sheet, int startRowNum, int lastRowNum) {
		ArrayList<Row> rows = new ArrayList<Row>();

		for (int i = startRowNum; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);


			if (Table2ColumnsOfSheetService.isNullRow(row)) continue;


			rows.add(row);

		}

		return rows;
	}

	public static String getCellValue(Cell cell) {

		if (cell == null) {
			return "";
		}
		if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
//        	if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
			String cellValue = String.valueOf(cell.getNumericCellValue());
			if (DateUtil.isCellDateFormatted(cell)) {
				// 如果是日期，则转换字符串后再返回
				return convertExcelDateToString(cellValue.substring(0, cellValue.length() - 2));
			} else if (cellValue.endsWith(".0")) {
				// 整数，去掉小数点后返回
				return cellValue.substring(0, cellValue.length() - 2);
			} else {
				// 浮点数
				return cellValue;
			}
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/**
	 * 把Excel的日期转换成字符串
	 *
	 * @param cellValue
	 * @return
	 */
	private static String convertExcelDateToString(String cellValue) {
		GregorianCalendar originCalendar = new GregorianCalendar(1900, 0, -1);
		originCalendar.add(Calendar.DATE, Integer.parseInt(cellValue));
		String nowYear = String.valueOf(originCalendar.get(Calendar.YEAR));
		String nowMonth = String.valueOf(originCalendar.get(Calendar.MONTH) + 1);
		String nowDate = String.valueOf(originCalendar.get(Calendar.DATE));
		return nowYear + "年" + nowMonth + "月" + nowDate + "日";
	}

	public static Workbook getWorkbook(String filePath) {
		Workbook workbook = null;
		InputStream inputStream = null;
		System.out.println("---准备导入列的文件名是：" + filePath);
//		D:\EMA工作目录\CrawlerFile\河南省\不合格信息\河南省​关于5批次食品不合格情况的通告（2021年第27期）_食品安全监督抽检产品合格信息_2021-07-07.xlsx
//		D:/EMA工作目录/CrawlerFile/河南省/不合格信息/河南省​关于5批次食品不合格情况的通告（2021年第27期）_食品安全监督抽检产品合格信息_2021-07-07.xlsx
		try {
			inputStream = new FileInputStream(filePath);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			if (filePath.endsWith(EXTENSION_XLS)) {
				workbook = new HSSFWorkbook(inputStream);
			} else if (filePath.endsWith(EXTENSION_XLSX)) {

//				OPCPackage pkg = OPCPackage.open(filePath);
//				workbook = new Workbook(pkg);
//			       // work with the wb object
//			       pkg.close(); // gracefully closes the underlying zip file

				workbook = new XSSFWorkbook(inputStream);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// LOG.debug("");
		// LOG.info("");
		return workbook;
	}

	public static List<String> getRowContent(Sheet sheet, int rowNum) {
		Row row = sheet.getRow(rowNum);
		List<String> rowContent = new ArrayList<String>();
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i);
			String cellValue = getCellValue(cell);
			if (cell != null) {
				if (CellType.NUMERIC == cell.getCellType()) {
					int format = cell.getCellStyle().getDataFormat();
					if (HSSFDateUtil.isCellDateFormatted(cell) || format == 31 || format == 57 || format == 58
							|| format == 14) {
						SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
						Date date = cell.getDateCellValue();
						cellValue = simpleDateFormat.format(date);
					} else if (format == 20 || format == 32) {
						SimpleDateFormat simpleDateFormat = new SimpleDateFormat("HH:mm");
						Date date = cell.getDateCellValue();
						cellValue = simpleDateFormat.format(date);
					}

				}
			}
			if (cellValue == null || cellValue.length() == 0 || cellValue == "") {
				rowContent.add("空");
			} else {
				rowContent.add(cellValue);
			}
			if (isMergedRegion(sheet, rowNum, i) != -1) {
				i = isMergedRegion(sheet, rowNum, i);
			}
		}
		return rowContent;
	}

	/**
	 * 判断单元格是否为合并单元格，如果是则返回合并单元格最后一列的列号，如果不是则返回-1
	 *
	 * @param sheet  表单号
	 * @param row    行号
	 * @param column 列号
	 * @return
	 */
	private static int isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return lastColumn;
				}
			}
		}
		return -1;
	}


	/**
	 *  检查dataCollection是否只有1条数据，并把唯一的数据返回
	 * @param dataCollection
	 * @return
	 * @throws Exception
	 */
	public static <T> T getOne(Collection<T> dataCollection) throws Exception {
//		System.out.println("应该是单个数据，但是实际从数据库中查找的数据数量是："+dataCollection.size());
		if (dataCollection == null || dataCollection.size() != 1)
			throw new Exception("******集合长度是："+dataCollection.size() + "，没有精确匹配成功，这一行的数据不能被导入");

		return (T) dataCollection.toArray()[0];
	}

	/**
	 * 打印行
	 * @param rows
	 */
	public static void print(List<Row> rows) {
		for (Row row : rows) {
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				System.out.print(getCellString(row, i) + "==");
			}
			System.out.println();
		}
	}


    public static Date parse(String time) {
        try {
            Date date = DateUtil.getJavaDate(Double.parseDouble(time));
            return date;
        } catch (Exception e) {
            return null;
        }
    }

    public static Calendar parse(Date time) {
    	try {
    		Calendar date = Calendar.getInstance();
    		date.setTime(time);
    		return date;
    	} catch (Exception e) {
    		return null;
    	}
    }


	public static String getCellString(Row row, int cellNum) {

//		System.out.println("行：" +row+ "，列：" + cellNum);

		Cell cell = row.getCell(cellNum);
		String adultertypeStr = null;


		if (cell == null) return null;
//		System.out.println("单元格的类型是：" + cell.getCellType());
		switch (cell.getCellType()) {
		case NUMERIC:
			adultertypeStr = String.valueOf(cell.getNumericCellValue());
			break;
		case STRING:
			adultertypeStr = cell.getStringCellValue();
			break;

//		case DATE:
//			adultertypeStr = cell.getDateCellValue();
//			break;

		default:
			break;
		}

		return adultertypeStr;
	}

	public static List<String> splitAddressName(String name) {
		List<String> list = new ArrayList<String>();
		// 正则表达式注意事项：matcher.matches()对于有分组的表达式，先匹配零组（即整句），在分组后加？可控制分组出现一次或零次
		String normalCNAddress = "(?<province>[^省]+省|.*?自治区|.*?特别行政区)?(?<city>[^市]+自治州|.*?地区|.*?行政单位|.+盟|市辖区|.*?市)?(?<county>[^县]+县|.+区|.+市|.+旗|.+海域|.+岛)?(?<town>[^镇]+镇|.*?街道)?(?<village>[^村]+村)?";
		Matcher matcher = Pattern.compile(normalCNAddress).matcher(name);
		String province = null, city = null, county = null, town = null, village = null;
		if (matcher.matches()) {
			province = matcher.group("province");
			city = matcher.group("city");
			county = matcher.group("county");
			town = matcher.group("town");
			village = matcher.group("village");
			if (province != null) {
				list.add(province);
			}
			if (city != null) {
				list.add(city);
			}
			if (county != null) {
				list.add(county);
			}
			if (town != null) {
				list.add(town);
			}
			if (village != null) {
				list.add(village);
			}
		}
		return list;
	}


	/**
	 * 如果第一列字体标红，那一行就不会录入
	 * @param rawList
	 * @param map
	 * @return
	 */
	public static List<Row> getAllRows(List<Row> rawList, Map<Integer, List<String>> map) {
		Workbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet();
		List<Row> rows = new ArrayList<>();
		int rowNum = 0;
		for (Row row : rawList) {
			if (isRed(row.getCell(0))) continue;
			List<Row> list = splitRow(sheet, rowNum, row, 0, map, new HashMap<Integer, String>());
			rowNum += list.size();
			rows.addAll(list);

		}

		return rows;
	}

	public static boolean isRed(Cell cell) {
		try {
			CellStyle style = cell.getCellStyle();
			if (style instanceof XSSFCellStyle) {
				if (((XSSFCellStyle) style).getFont().getXSSFColor().getARGBHex().equals("FFFF0000")) {
					return true;
				}
			} else if (style instanceof HSSFCellStyle) {
				HSSFFont font = ((HSSFCellStyle) style).getFont(cell.getRow().getSheet().getWorkbook());
				HSSFColor hssfColor = font.getHSSFColor((HSSFWorkbook) cell.getRow().getSheet().getWorkbook());
				if (hssfColor.getHexString().equals("FFFF:0:0")) {
					return true;
				}
			}
		} catch (Exception e) {
		}
		return false;
	}

	private static List<Row> splitRow(Sheet sheet, int rowNum, Row row, int cellNum,
			Map<Integer, List<String>> splitRuleMap, Map<Integer, String> xlsColumnNumberContentMap) {
		List<Row> rows = new ArrayList<>();
		List<String> splitList = splitRuleMap.get(cellNum);

		List<String> splitStrings = getSplitStrings(getCellString(row, cellNum), splitList);

		splitStrings = getRightSplit(splitStrings.toArray(new String[0]));

		for (String str : splitStrings) {
			if (str != null)
				str = str.replace('\u00A0', ' ').trim();
			xlsColumnNumberContentMap.put(cellNum, str);
			if (cellNum < row.getLastCellNum() - 1) {
//				递归调用
				List<Row> tmpList = splitRow(sheet, rowNum, row, cellNum + 1, splitRuleMap, xlsColumnNumberContentMap);
				rowNum += tmpList.size();
				rows.addAll(tmpList);
			} else { // 最后一列
				rows.add(fillingRow(sheet.createRow(rowNum), xlsColumnNumberContentMap));
				rowNum++;
			}

		}

		return rows;
	}

	/**
	 * 处理沈老师表格的掺入物名称，例如 棕榈油提取物废物，牛奶（液体，不详），甜味剂（不详） 修复根据'，'分割字符串时，在括号里分割的错误
	 *
	 * @param strings 待处理的字符串集
	 * @return
	 */
	public static List<String> getRightSplit(String... strings) {
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < strings.length; i++) {
			String s = strings[i];
			if (s == null || !ExcelUtil.isContain(s, "（") || ExcelUtil.isContain(s, "（", "）")) {
				list.add(s);
				continue;
			}
			i++;
			while (i < strings.length) {
				s = s + "，" + strings[i];
				if (ExcelUtil.isContain(s, "（", "）")) {
					list.add(s);
					break;
				}
				i++;
			}
		}
		return list;
	}

	private static Row fillingRow(Row row, Map<Integer, String> attrs) {
		for (Entry<Integer, String> e : attrs.entrySet()) {
			Cell cell = row.createCell(e.getKey());
			cell.setCellValue(e.getValue());
		}
		return row;
	}

	public static List<String> getSplitStrings(String cellString, List<String> regexList) {
		List<String> list = new ArrayList<>();
		list.add(cellString);
		if (regexList == null) {
			return list;
		}

		for (String regex : regexList) {
			list = getSplitStrings(list, regex);
		}

		return list;
	}

	private static List<String> getSplitStrings(List<String> list, String regex) {
		List<String> l = new ArrayList<>();

		for (String s : list) {
			if (s == null && list.size() == 1)
				l.add(s);
			else
				l.addAll(getList(s.split(regex)));
		}

		return l;
	}

	private static <T> List<T> getList(T ...ts) {
		List<T> list = new ArrayList<>();
		for (T t : ts) {
			list.add(t);
		}
		return list;
	}


	/**
	 * 判断字符串s里是否(按顺序)包含keys里的字符串
	 * @param s		待检测的字符串
	 * @param keys
	 * @return
	 */
	public static boolean isContain(String s, String ...keys) {
		// 记录key在s的下标
		List<Integer> indexs = new ArrayList<>();
		for (String key : keys) {
			int indexOf = s.indexOf(key);
			if (indexOf == -1) return false;
			for (Integer index : indexs) {
				if (index >= indexOf) return false;
			}
			indexs.add(indexOf);
		}
		return true;
	}

	public static List<Row> getRedRows(List<Row> rawDataList) {
		List<Row> list = new ArrayList<Row>();

		for (Row row : rawDataList) {
			Cell cell = row.getCell(0);
			if (isRed(cell))
				list.add(row);
		}

		return list;
	}

//	把数据保存到指定的文件中的行中，其实可以不用使用功能第一个参数的。
	public static void saveRow(String testWorkbook, Row row, Map<Integer, String> valueMap) throws Throwable {
		for(Integer key: valueMap.keySet()) {
			Cell cell = row.getCell(key);
			cell.setCellValue(valueMap.get(key));
		}
		FileOutputStream ops = new FileOutputStream(testWorkbook);
		row.getSheet().getWorkbook().write(ops);
		ops.flush();
		ops.close();
	}

	public static String getCellStringFromNum(Row row, int i) {
		// TODO Auto-generated method stub
		Cell cell = row.getCell(i);
		cell.setCellType(CellType.STRING);
		String value = cell.getStringCellValue();

		return value;
	}
}
